package com.neusoft.elm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.elm.dao.OrdersDao;
import com.neusoft.elm.po.Business;
import com.neusoft.elm.po.Orders;
import com.neusoft.elm.util.CommonUtil;
import com.neusoft.elm.util.DBUtil;

/**
 * 订单数据访问层实现类
 */
public class OrdersDaoImpl implements OrdersDao {
    private Connection con = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;

    @Override
    public int saveOrders(Orders orders) throws Exception {
        int result = 0;
        String sql = "insert into orders(userId,businessId,orderDate,orderTotal,daId,orderState,contactName,contactSex,contactTel,address) values(?,?,?,?,?,1,?,?,?,?)";
        try {
            con = DBUtil.getConnection();
            // 设置返回生成的主键值
            pst = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pst.setString(1, orders.getUserId());
            pst.setInt(2, orders.getBusinessId());
            pst.setString(3, CommonUtil.getCurrentDate());
            pst.setDouble(4, orders.getOrderTotal());
            pst.setInt(5, orders.getDaId());
            pst.setString(6, orders.getContactName());
            pst.setObject(7, orders.getContactSex());
            pst.setString(8, orders.getContactTel());
            pst.setString(9, orders.getAddress());
            
            pst.executeUpdate();
            
            // 获取生成的主键值
            rs = pst.getGeneratedKeys();
            if (rs.next()) {
                result = rs.getInt(1);
            }
            System.out.println("保存订单成功，包含配送地址快照数据，订单ID: " + result);
        } finally {
            DBUtil.close(pst);
        }
        return result;
    }

    @Override
    public Orders getOrdersById(Integer orderId) throws Exception {
        Orders orders = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" select o.*, ");
        sql.append("        b.businessId bbusinessId, ");
        sql.append("        b.businessName bbusinessName, ");
        sql.append("        b.deliveryPrice bdeliveryPrice ");
        sql.append(" from orders o left join business b on o.businessId=b.businessId ");
        sql.append(" where o.orderId=? ");
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            pst.setInt(1, orderId);
            rs = pst.executeQuery();
            if (rs.next()) {
                orders = new Orders();
                orders.setOrderId(rs.getInt("orderId"));
                orders.setUserId(rs.getString("userId"));
                orders.setBusinessId(rs.getInt("businessId"));
                orders.setOrderDate(rs.getString("orderDate"));
                orders.setOrderTotal(rs.getDouble("orderTotal"));
                orders.setDaId(rs.getInt("daId"));
                orders.setOrderState(rs.getInt("orderState"));
                
                orders.setContactName(rs.getString("contactName"));
                orders.setContactSex(rs.getObject("contactSex") != null ? rs.getInt("contactSex") : null);
                orders.setContactTel(rs.getString("contactTel"));
                orders.setAddress(rs.getString("address"));
                
                // 设置关联的商家信息
                Business business = new Business();
                business.setBusinessId(rs.getInt("bbusinessId"));
                business.setBusinessName(rs.getString("bbusinessName"));
                business.setDeliveryPrice(rs.getDouble("bdeliveryPrice"));
                orders.setBusiness(business);
            }
        } finally {
            DBUtil.close(rs, pst);
        }
        return orders;
    }

    @Override
    public List<Orders> listOrdersByUserId(String userId) throws Exception {
        List<Orders> list = new ArrayList<>();
        StringBuffer sql = new StringBuffer();
        sql.append(" select o.*, ");
        sql.append("        b.businessId bbusinessId, ");
        sql.append("        b.businessName bbusinessName, ");
        sql.append("        b.deliveryPrice bdeliveryPrice ");
        sql.append(" from orders o left join business b on o.businessId=b.businessId ");
        sql.append(" where o.userId=? ");
        sql.append(" order by o.orderDate desc ");
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            pst.setString(1, userId);
            rs = pst.executeQuery();
            while (rs.next()) {
                Orders orders = new Orders();
                orders.setOrderId(rs.getInt("orderId"));
                orders.setUserId(rs.getString("userId"));
                orders.setBusinessId(rs.getInt("businessId"));
                orders.setOrderDate(rs.getString("orderDate"));
                orders.setOrderTotal(rs.getDouble("orderTotal"));
                orders.setDaId(rs.getInt("daId"));
                orders.setOrderState(rs.getInt("orderState"));
                
                orders.setContactName(rs.getString("contactName"));
                orders.setContactSex(rs.getObject("contactSex") != null ? rs.getInt("contactSex") : null);
                orders.setContactTel(rs.getString("contactTel"));
                orders.setAddress(rs.getString("address"));
                
                // 设置关联的商家信息
                Business business = new Business();
                business.setBusinessId(rs.getInt("bbusinessId"));
                business.setBusinessName(rs.getString("bbusinessName"));
                business.setDeliveryPrice(rs.getDouble("bdeliveryPrice"));
                orders.setBusiness(business);
                
                list.add(orders);
            }
        } finally {
            DBUtil.close(rs, pst);
        }
        return list;
    }

    @Override
    public List<Orders> listOrdersByBusinessId(Integer businessId) throws Exception {
        List<Orders> list = new ArrayList<>();
        StringBuffer sql = new StringBuffer();
        sql.append(" select o.*, ");
        sql.append("        b.businessId bbusinessId, ");
        sql.append("        b.businessName bbusinessName, ");
        sql.append("        b.deliveryPrice bdeliveryPrice ");
        sql.append(" from orders o left join business b on o.businessId=b.businessId ");
        sql.append(" where o.businessId=? ");
        sql.append(" order by o.orderDate desc ");
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            pst.setInt(1, businessId);
            rs = pst.executeQuery();
            while (rs.next()) {
                Orders orders = new Orders();
                orders.setOrderId(rs.getInt("orderId"));
                orders.setUserId(rs.getString("userId"));
                orders.setBusinessId(rs.getInt("businessId"));
                orders.setOrderDate(rs.getString("orderDate"));
                orders.setOrderTotal(rs.getDouble("orderTotal"));
                orders.setDaId(rs.getInt("daId"));
                orders.setOrderState(rs.getInt("orderState"));
                
                orders.setContactName(rs.getString("contactName"));
                orders.setContactSex(rs.getObject("contactSex") != null ? rs.getInt("contactSex") : null);
                orders.setContactTel(rs.getString("contactTel"));
                orders.setAddress(rs.getString("address"));
                
                // 设置关联的商家信息
                Business business = new Business();
                business.setBusinessId(rs.getInt("bbusinessId"));
                business.setBusinessName(rs.getString("bbusinessName"));
                business.setDeliveryPrice(rs.getDouble("bdeliveryPrice"));
                orders.setBusiness(business);
                
                list.add(orders);
            }
        } finally {
            DBUtil.close(rs, pst);
        }
        return list;
    }

    @Override
    public int updateOrderState(Integer orderId, Integer orderState) throws Exception {
        int result = 0;
        String sql = "update orders set orderState=? where orderId=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, orderState);
            pst.setInt(2, orderId);
            result = pst.executeUpdate();
            System.out.println("更新订单状态 - 订单ID: " + orderId + ", 新状态: " + orderState + ", 影响行数: " + result);
        } finally {
            DBUtil.close(pst);
        }
        return result;
    }
}